package com.chart.common.utils;

import com.chart.common.constant.WeChatConstants;
import org.apache.commons.math3.stat.regression.SimpleRegression;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 处理读取到的excel数据
 *
 * @author chart
 */


public class ExcelDataUtils
{

    //横着读取表格数据
    public static List<List<String>> getData (Sheet sheet){
        List<List<String>> newlist = new ArrayList<>();
        int lastRowNum = sheet.getLastRowNum();

        FormulaEvaluator formulaEvaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
//        int lastCellNum = sheet.getRow(9).getLastCellNum();

//        DataFormatter dataFormatter = new DataFormatter();
        // 建议在类的顶部定义 DecimalFormat 对象
        DecimalFormat decimalFormat = new DecimalFormat("#");
        for (int i = 8; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                break;
            }
            short lastCellNum = row.getLastCellNum();
            //判断表格数据大小
            int i1 = lastRowNum * lastCellNum;
            if (i1 > 40000) {
                throw new RuntimeException(WeChatConstants.DATA_MAX);
            }
            List<String> list = new ArrayList<>();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null || cell.getCellType() == CellType.BLANK) {
                    list.add("");
                } else {
                    // 判断第一列数据特殊处理
                    if (j == 0 && cell.getCellType() == CellType.NUMERIC) {
                        // 如果是数字类型，则判断是否为整数
                        double numericValue = cell.getNumericCellValue();
                        if (numericValue == (int) numericValue) {
                            // 如果是整数，则转换为整数形式
                            list.add(decimalFormat.format(numericValue));
                        } else {
                            // 如果不是整数，则按照原逻辑处理
                            list.add(String.valueOf(numericValue));
                        }
                    } else {
                        // 其他类型按照原逻辑处理
                        switch (cell.getCellType()) {
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    // 如果是日期时间类型，将其转换为指定格式的字符串
                                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/M/d HH:mm:ss");
                                    String formattedDate = dateFormat.format(cell.getDateCellValue());
                                    list.add(formattedDate);
                                } else {
                                    // 如果是数值类型，则直接添加数值
                                    list.add(String.valueOf(cell.getNumericCellValue()));
                                }
                                break;
                            case STRING:
                                // 如果是字符串类型，则直接添加字符串
                                list.add(cell.getStringCellValue());
                                break;
                            case BOOLEAN:
                                // 如果是布尔类型，则直接添加布尔值
                                list.add(String.valueOf(cell.getBooleanCellValue()));
                                break;
                            case FORMULA:
                                // 如果是公式类型，则计算公式结果并添加
                                CellValue cellValue = formulaEvaluator.evaluate(cell);
                                switch (cellValue.getCellType()) {
                                    case NUMERIC:
                                        list.add(String.valueOf(cellValue.getNumberValue()));
                                        break;
                                    case STRING:
                                        list.add(cellValue.getStringValue());
                                        break;
                                    case BOOLEAN:
                                        list.add(String.valueOf(cellValue.getBooleanValue()));
                                        break;
                                    case ERROR:
                                        list.add(String.valueOf(cellValue.getErrorValue()));
                                        break;
                                    default:
                                        list.add("");
                                        break;
                                }
                                break;
                            case ERROR:
                                // 如果是错误类型，则添加错误信息
                                list.add(String.valueOf(cell.getErrorCellValue()));
                                break;
                            default:
                                // 其他类型一律添加空字符串
                                list.add("");
                                break;
                        }
                    }
                }
            }
            newlist.add(list);
        }
        return newlist;
    }


    //竖着读取表格数据
    public static List<List<String>> getColData(Sheet sheet) {
        List<List<String>> newlist = new ArrayList<>();
        int lastRowNum = sheet.getLastRowNum();  // 获取最后一行的索引
        int lastCellNum = sheet.getRow(9).getLastCellNum();  // 获取最后一列的索引
        FormulaEvaluator formulaEvaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();

        //判断表格数据大小
        int i1 = lastCellNum * lastRowNum;
        if (i1 > 40000) {
            throw new RuntimeException("数据量超过最大限制");
        }
        // 建议在类的顶部定义 DecimalFormat 对象
        DecimalFormat decimalFormat = new DecimalFormat("#");
        for (int j = 0; j < lastCellNum; j++) {  // 遍历每一列
            List<String> list = new ArrayList<>();
            for (int i = 8; i <= lastRowNum; i++) {  // 遍历每一行
                Row row = sheet.getRow(i);
                if (row == null) {  // 如果行为空，则在新列表中对应位置添加空字符串
                    list.add("");
                    break;
                } else {
                    Cell cell = row.getCell(j);
                    if (cell == null || cell.getCellType() == CellType.BLANK) {  // 如果单元格为空，则在新列表中对应位置添加空字符串
                        list.add("");
                    } else {
                        // 判断单元格类型并相应处理
                        if (j == 0 && cell.getCellType() == CellType.NUMERIC) {
                            // 如果是第一列且是数字类型，则判断是否为整数
                            double numericValue = cell.getNumericCellValue();
                            if (numericValue == (int) numericValue) {
                                // 如果是整数，则转换为整数形式
                                list.add(decimalFormat.format(numericValue));
                            } else {
                                // 如果不是整数，则按照原逻辑处理
                                list.add(String.valueOf(numericValue));
                            }
                        } else {
                            switch (cell.getCellType()) {
                                case NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        // 如果是日期时间类型，将其转换为指定格式的字符串
                                        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/M/d HH:mm:ss");
                                        String formattedDate = dateFormat.format(cell.getDateCellValue());
                                        list.add(formattedDate);
                                    } else {
                                        // 如果是数值类型，则直接添加数值
                                        list.add(String.valueOf(cell.getNumericCellValue()));
                                    }
                                    break;
                                case STRING:
                                    // 如果是字符串类型，则直接添加字符串
                                    list.add(cell.getStringCellValue());
                                    break;
                                case BOOLEAN:
                                    // 如果是布尔类型，则直接添加布尔值
                                    list.add(String.valueOf(cell.getBooleanCellValue()));
                                    break;
                                case FORMULA:
                                    // 如果是公式类型，则计算公式结果并添加
                                    CellValue cellValue = formulaEvaluator.evaluate(cell);
                                    switch (cellValue.getCellType()) {
                                        case NUMERIC:
                                            list.add(String.valueOf(cellValue.getNumberValue()));
                                            break;
                                        case STRING:
                                            list.add(cellValue.getStringValue());
                                            break;
                                        case BOOLEAN:
                                            list.add(String.valueOf(cellValue.getBooleanValue()));
                                            break;
                                        case ERROR:
                                            list.add(String.valueOf(cellValue.getErrorValue()));
                                            break;
                                        default:
                                            list.add("");
                                            break;
                                    }
                                    break;
                                case ERROR:
                                    // 如果是错误类型，则添加错误信息
                                    list.add(String.valueOf(cell.getErrorCellValue()));
                                    break;
                                default:
                                    // 其他类型一律添加空字符串
                                    list.add("");
                                    break;
                            }
                        }
                    }
                }
            }
            System.out.println("list:" + list);
            newlist.add(list);  // 将每一列的数据行添加到新列表中
        }
        return newlist;
    }


    // 获取每一列的数值的和
    public static List<Double> getColDataSum (Sheet sheet){
        List<Double> newlist = new ArrayList<>();
        int lastRowNum = sheet.getLastRowNum();  // 获取最后一行的索引
        int lastCellNum = sheet.getRow(10).getLastCellNum();  // 获取最后一列的索引


        for (int j = 1; j < lastCellNum; j++) {  // 遍历每一列
            double colData=0;
            for (int i = 11; i <= lastRowNum; i++) {  // 遍历每一行
                Row row = sheet.getRow(i);
                if (row == null) {  // 如果行为空，则在新列表中对应位置添加空字符串
                    colData +=0;
                } else {
                    Cell cell = row.getCell(j);
                    if (cell == null || cell.getCellType() == CellType.BLANK) {  // 如果单元格为空，则在新列表中对应位置添加空字符串
                        colData +=0;
                    } else {
                        double v = Double.parseDouble(cell.toString());
                        colData += v;
                    }
                }
            }
            newlist.add(colData);  // 将每一列的数据行添加到新列表中
        }
        return newlist;
    }

    // 获取每一行的数值的和
    public static List<Double> getRowDataSum (Sheet sheet){
        List<Double> newlist = new ArrayList<>();
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 11; i < lastRowNum+1; i++) {
            double rowData=0;
            Row row = sheet.getRow(i);
            if (row == null) {
                break;
            }
            for (int j = 1; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null || cell.getCellType() == CellType.BLANK) {
                    rowData +=0;
                }else {
                    double v = Double.parseDouble(cell.toString());
                    rowData +=v;
                }
            }
            newlist.add(rowData);
        }
        return newlist;
    }

    //计算出线性回归方程   &&找出最大最小值点对应方程的值
    public static Map getService1 (double[] x, double[] y){
//        x = {5, 16, 13, 8, 9,11,11,12};
//        y = {3, 4, 5, 7, 7,8,9,10};

        // 创建简单回归模型
        SimpleRegression regression = new SimpleRegression();

        // 将数据添加到模型中
        for (int i = 0; i < x.length; i++) {
            regression.addData(x[i], y[i]);
        }

        // 获取斜率和截距
        double slope = regression.getSlope();
        double intercept = regression.getIntercept();

        // 保留小数点后两位
        slope = Math.round(slope * 100.0) / 100.0;
        intercept = Math.round(intercept * 100.0) / 100.0;

        // 打印回归方程
        HashMap<String, Object> map = new HashMap<>();

        map.put("formatter","y=" + slope + " * x + " + intercept);

        // 获取最大值和最小值对应的点
        double maxX = x[0];
        double maxY = y[0];
        double minX = x[0];
        double minY = y[0];

        for (int i = 1; i < x.length; i++) {
            if (x[i] > maxX) {
                maxX = x[i];
            }
            if (x[i] < minX) {
                minX = x[i];
            }
        }

        maxY = slope * maxX + intercept;
        minY = slope * minX + intercept;

        maxY = Math.round(maxY * 100000.0) / 100000.0;
        minY = Math.round(minY * 100000.0) / 100000.0;
        // 打印最大值和最小值对应的点
        for (int i = 0; i < 2; i++) {
            ArrayList<Double> doubles = new ArrayList<>();
            if (i==0) {
                doubles.add(maxX);
                doubles.add(maxY);
                map.put("max",doubles);
            }else {
                doubles.add(minX);
                doubles.add(minY);
                map.put("min",doubles);
            }


        }
        return map;
    }

    //求出每个点对应的方差
    public static List<List<String>> getService2 (double[] x, double[] y){
//        x = {5, 16, 13, 8, 9,11,11,12};
//        y = {3, 4, 5, 7, 7,8,9,10};

        // 创建简单回归模型
        SimpleRegression regression = new SimpleRegression();

        // 将数据添加到模型中
        for (int i = 0; i < x.length; i++) {
            regression.addData(x[i], y[i]);
        }

        // 获取斜率和截距
        double slope = regression.getSlope();
        double intercept = regression.getIntercept();

        // 保留小数点后两位
        slope = Math.round(slope * 100.0) / 100.0;
        intercept = Math.round(intercept * 100.0) / 100.0;

        // 打印回归方程
//        HashMap<String, Object> map = new HashMap<>();
//
//        map.put("formatter","y=" + slope + " * x + " + intercept);

        // 计算残差
        List<List<String>> newList = new ArrayList<>();
        double[] residuals = new double[x.length];
        for (int i = 0; i < x.length; i++) {
            List<String> doubles = new ArrayList<>();
            residuals[i] = y[i] - (slope * x[i] + intercept);
            doubles.add(String.valueOf(x[i]));
            doubles.add(String.valueOf(residuals[i]));
            newList.add(doubles);
        }

        return newList;
    }

    //求出最大值（绝对值）并返回把最大值最高位+1的数值
    public static int Max(List<String> data){
        // 使用自定义比较器按数值大小获取最大值和最小值
        String max = Collections.max(data, new Comparator<String>() {
            @Override
            public int compare(String s1, String s2) {
                return Double.compare(Double.parseDouble(s1), Double.parseDouble(s2));
            }
        });

        String min = Collections.min(data, new Comparator<String>() {
            @Override
            public int compare(String s1, String s2) {
                return Double.compare(Double.parseDouble(s1), Double.parseDouble(s2));
            }
        });

        int maxNum = Math.abs((int) Double.parseDouble(max));
        int minNum = Math.abs((int) Double.parseDouble(min));

        // 获取 max 和 min 的绝对值并转换为字符串
        String maxStr = String.valueOf(maxNum);
        String minStr = String.valueOf(minNum);
        // 获取 max 和 min 的首位数字并加 1
        int maxFirstDigitPlusOne = Integer.parseInt(maxStr.substring(0, 1)) + 1;
        int minFirstDigitPlusOne = Integer.parseInt(minStr.substring(0, 1)) + 1;

        // 构造新的字符串
        StringBuilder maxResultStr = new StringBuilder(String.valueOf(maxFirstDigitPlusOne));
        StringBuilder minResultStr = new StringBuilder(String.valueOf(minFirstDigitPlusOne));

        // 根据原始字符串的长度添加相应数量的零
        for (int i = 0; i < maxStr.length() - 1; i++) {
            maxResultStr.append('0');
        }
        for (int i = 0; i < minStr.length() - 1; i++) {
            minResultStr.append('0');
        }

        // 转换回整数类型
        int maxResult = Integer.parseInt(maxResultStr.toString());
        int minResult = Integer.parseInt(minResultStr.toString());

        // 返回结果
        return maxNum > minNum ? maxResult : minResult;
    }


    public static int Min(List<String> data){

        // 使用自定义比较器按数值大小获取最大值和最小值
        String max = Collections.max(data, new Comparator<String>() {
            @Override
            public int compare(String s1, String s2) {
                return Double.compare(Double.parseDouble(s1), Double.parseDouble(s2));
            }
        });

        String min = Collections.min(data, new Comparator<String>() {
            @Override
            public int compare(String s1, String s2) {
                return Double.compare(Double.parseDouble(s1), Double.parseDouble(s2));
            }
        });

        int maxNum = Math.abs((int) Double.parseDouble(max));
        int minNum = Math.abs((int) Double.parseDouble(min));

        // 获取 max 和 min 的绝对值并转换为字符串
        String maxStr = String.valueOf(maxNum);
        String minStr = String.valueOf(minNum);

        // 获取 max 和 min 的首位数字并加 1
        int maxFirstDigitPlusOne = Integer.parseInt(maxStr.substring(0, 1)) + 1;
        int minFirstDigitPlusOne = Integer.parseInt(minStr.substring(0, 1)) + 1;

        // 构造新的字符串
        StringBuilder maxResultStr = new StringBuilder(String.valueOf(maxFirstDigitPlusOne));
        StringBuilder minResultStr = new StringBuilder(String.valueOf(minFirstDigitPlusOne));

        // 根据原始字符串的长度添加相应数量的零
        for (int i = 0; i < maxStr.length() - 1; i++) {
            maxResultStr.append('0');
        }
        for (int i = 0; i < minStr.length() - 1; i++) {
            minResultStr.append('0');
        }

        // 转换回整数类型
        int maxResult = Integer.parseInt(maxResultStr.toString());
        int minResult = Integer.parseInt(minResultStr.toString());

        // 返回结果
        if (Double.parseDouble(min) >= 0) {
            return 0;
        } else {
            return maxNum > minNum ? -maxResult : -minResult;
        }
    }


    //求最大值&&最小值
    public static Result findMinMax(List<String> data) {
        if (data == null || data.isEmpty()) {
            throw new IllegalArgumentException("List must not be null or empty");
        }

        int min = Integer.MAX_VALUE;
        int max = Integer.MIN_VALUE;

        for (String str : data) {
            int num;
            try {
                num = (int) Double.parseDouble(str);
            } catch (NumberFormatException e) {
                throw new IllegalArgumentException("List must contain valid integer strings");
            }

            if (num < min) {
                min = num;
            }
            if (num > max) {
                max = num;
            }
        }

        return new Result(min, max);
    }

    public static class Result {
        public int min;
        public int max;

        public Result(int min, int max) {
            this.min = min;
            this.max = max;
        }
    }




    public static Sheet convertDataListToSheetsa(List<Map<String, Object>> dataList) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        for (int i = 0; i < 8; i++) {
            sheet.createRow(i);
        }

        int rowIndex = 8;
        for (Map<String, Object> data : dataList) {
            Row row = sheet.createRow(rowIndex++);
            int cellIndex = 0;
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                Cell cell = row.createCell(cellIndex++);
                setCellValues(cell, entry.getValue());
            }
        }

        return sheet;
    }

    private static void setCellValues(Cell cell, Object value) {
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else {
            cell.setCellValue(value != null ? value.toString() : "");
        }
    }
}
